import os
from flask import Blueprint, render_template, request, redirect, url_for, flash, session
from database import get_db
from routes.inventory_category import get_all_inventory_categories
from routes.model_category import get_all_model_categories
from routes.inventory_models import inventory_models_bp

inventory_bp = Blueprint('inventory', __name__, url_prefix='/inventories')

# 碳盘查列表页
@inventory_bp.route('/')
def inventory_list():
    search_query = request.args.get('search', '').strip()
    sort_by = request.args.get('sort', 'created_at')  # 默认按创建时间排序
    sort_order = request.args.get('order', 'desc')    # 默认降序
    db = get_db()
    
    # 定义允许的排序字段，防止SQL注入
    allowed_sort_fields = ['name', 'start_time', 'end_time', 'status', 'created_at']
    if sort_by not in allowed_sort_fields:
        sort_by = 'created_at'
    
    # 定义状态标签样式映射函数
    def status_badge_class(status):
        status_map = {
            '新建': 'bg-secondary',
            '执行': 'bg-primary',
            '完成': 'bg-success'
        }
        return status_map.get(status, 'bg-secondary')
    
    try:
        # 构建查询条件
        query_params = []
        where_clauses = []
        
        if search_query:
            where_clauses.append('(ci.name LIKE ? OR mc.name LIKE ? OR ic.name LIKE ? OR ci.base_year LIKE ?)')
            query_params.extend([f'%{search_query}%'] * 4)
        
        base_sql = f'''
            SELECT ci.*, mc.name as model_category_name, 
                   ic.name as inventory_category_name
            FROM carbon_inventory ci
            LEFT JOIN model_categories mc ON ci.model_categories_id = mc.id
            LEFT JOIN inventory_categories ic ON ci.category_id = ic.id
        '''
        
        if where_clauses:
            base_sql += ' WHERE ' + ' AND '.join(where_clauses)
        
        # 添加排序逻辑
        base_sql += f' ORDER BY ci.{sort_by} {sort_order.upper()}'
        
        inventories = db.execute(base_sql, query_params).fetchall()
    except Exception as e:
        flash(f'数据库错误: {str(e)}', 'danger')
        inventories = []
    
    return render_template('inventories.html', 
                          inventories=inventories, 
                          search_query=search_query, 
                          status_badge_class=status_badge_class,
                          current_sort=sort_by,
                          current_order=sort_order)
# 添加碳盘查
@inventory_bp.route('/add', methods=['GET', 'POST'])
def add_inventory():
    db = get_db()
    
    if request.method == 'POST':
        # 获取表单数据
        name = request.form['name']
        start_time = request.form['start_time']
        end_time = request.form['end_time']
        organization_boundary = request.form['organization_boundary']
        base_year = request.form['base_year']
        accounting_standard = request.form['accounting_standard']
        accounting_method = request.form['accounting_method']
        model_categories_id = request.form['model_categories_id'] 
        category_id = request.form['category_id']
        belong_year = request.form.get('belong_year')
        belong_month = request.form.get('belong_month')
        last_modified_by = request.remote_addr
        
        # 验证必填字段
        if not all([name, start_time, end_time, organization_boundary, base_year, 
                   accounting_standard, accounting_method, model_categories_id, category_id,
                   belong_year, belong_month]):
            flash('请填写所有必填字段', 'danger')
            return redirect(url_for('inventory.add_inventory'))
        
        try:
            # 插入新盘查事项
            db.execute(
                'INSERT INTO carbon_inventory (name, start_time, end_time, organization_boundary, '
                'base_year, accounting_standard, accounting_method, model_categories_id, category_id, status, '
                'belong_year, belong_month) '
                'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
                (name, start_time, end_time, organization_boundary, base_year, 
                 accounting_standard, accounting_method, model_categories_id, category_id, '新建',
                 belong_year, belong_month)
            )
            db.commit()
            flash('碳盘查事项添加成功', 'success')
            return redirect(url_for('inventory.inventory_list'))
        except Exception as e:
            db.rollback()
            flash(f'添加失败: {str(e)}', 'danger')
    
    # GET请求 - 获取模型和分类数据
    model_categories = db.execute('''
        WITH RECURSIVE category_hierarchy AS (
            SELECT id, name, parent_id, 
                   CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                   1 AS level
            FROM model_categories
            WHERE parent_id = 0 OR parent_id IS NULL
            UNION ALL
            SELECT cc.id, cc.name, cc.parent_id, 
                   ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                   ch.level + 1 AS level
            FROM model_categories cc
            JOIN category_hierarchy ch ON cc.parent_id = ch.id
        )
        SELECT id, name, level, hierarchy_path
        FROM category_hierarchy
        ORDER BY hierarchy_path
    ''').fetchall()
    inventory_categories = get_all_inventory_categories()  # 使用递归查询获取所有分类
    
    return render_template('add_inventory.html', model_categories=model_categories, inventory_categories=inventory_categories)
    

# 盘查详情页
@inventory_bp.route('/detail/<int:id>')
def inventory_detail(id):
    db = get_db()
    
    # 获取盘查基本信息
    inventory = db.execute(
        'SELECT ci.*, mc.name as model_category_name, ic.name as inventory_category_name '
        'FROM carbon_inventory ci '
        'JOIN model_categories mc ON ci.model_categories_id = mc.id '
        'JOIN inventory_categories ic ON ci.category_id = ic.id '
        'WHERE ci.id = ?', (id,)
    ).fetchone()
    
    if not inventory:
        flash('碳盘查事项不存在', 'danger')
        return redirect(url_for('inventory.inventory_list'))
    
    # 获取关联排放源
    emission_sources = db.execute(
        'SELECT ies.*, m.emission_source, m.description, m.data_source, m.emission_unit, '
        'cf.name as factor_name, cf.emission_value '
        'FROM inventory_emission_sources ies '
        'JOIN models m ON ies.emission_source_id = m.id '
        'JOIN carbon_factors cf ON m.factor_id = cf.id '
        'WHERE ies.inventory_id = ?', (id,)
    ).fetchall()
    
    return render_template('inventory_detail.html', inventory=inventory, emission_sources=emission_sources)

@inventory_bp.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit(id):
    db = get_db()
    inventory = db.execute('SELECT * FROM carbon_inventory WHERE id = ?', (id,)).fetchone()
    
    if not inventory:
        flash('碳盘查记录不存在', 'danger')
        return redirect(url_for('inventory.inventory_list'))
    
    model_categories = []
    cursor = db.execute('SELECT id, name, level FROM model_categories')
    for row in cursor.fetchall():
        model_categories.append({
            'id': row['id'],
            'name': row['name'],
            'level': row['level']  # 必须显式转换为字典
        })
    
    # 库存分类保持不变（已通过get_all_inventory_categories获取字典列表）
    inventory_categories = get_all_inventory_categories()
    
    if request.method == 'POST':
        # 获取表单数据
        name = request.form['name']
        start_time = request.form['start_time']
        end_time = request.form['end_time']
        organization_boundary = request.form['organization_boundary']
        base_year = request.form['base_year']
        accounting_standard = request.form['accounting_standard']
        accounting_method = request.form['accounting_method']
        model_categories_id = request.form['model_categories_id']
        category_id = request.form['category_id']
        status = request.form['status']
        belong_year = request.form.get('belong_year')
        belong_month = request.form.get('belong_month')
        last_modified_by = session.get('username', 'admin')
        # 验证必填字段
        if not all([name, start_time, end_time, organization_boundary, base_year, 
                   accounting_standard, accounting_method, model_categories_id, category_id]):
            flash('请填写所有必填字段', 'danger')
            return redirect(url_for('inventory.edit', id=id))
        
        try:
            # 检查模型分类是否变更
            old_category_id = inventory['model_categories_id']
            category_changed = old_category_id != int(model_categories_id)
            
            # 更新盘查记录
            db.execute(
                'UPDATE carbon_inventory SET name=?, start_time=?, end_time=?, organization_boundary=?, '
                'base_year=?, accounting_standard=?, accounting_method=?, model_categories_id=?, '
                'category_id=?, status=?, belong_year=?, belong_month=?, last_modified_by=? WHERE id=?',
                (name, start_time, end_time, organization_boundary, base_year, 
                 accounting_standard, accounting_method, model_categories_id, category_id, status,
                 belong_year, belong_month, last_modified_by, id)
            )

            db.commit()
            flash('碳盘查记录已更新', 'success')
            return redirect(url_for('inventory.edit', id=id))
        except Exception as e:
            db.rollback()
            flash(f'更新失败: {str(e)}', 'danger')
    
    # 获取模型分类和碳盘查分类数据
    model_categories = get_all_model_categories()
    inventory_categories = get_all_inventory_categories()
    
    # 获取已关联的排放源
    emission_sources = db.execute(
        'SELECT ies.*, m.emission_source, m.description, m.data_source, m.emission_unit, '
        'cf.name as factor_name, cf.emission_value '
        'FROM inventory_emission_sources ies '
        'JOIN models m ON ies.emission_source_id = m.id '
        'JOIN carbon_factors cf ON m.factor_id = cf.id '
        'WHERE ies.inventory_id = ?', (id,)
    ).fetchall()
    
    # 获取基于模型分类的所有排放源（用于初始化显示）
    model_based_sources = []
    if inventory['model_categories_id']:
        # 使用递归CTE查询所有子分类ID
        category_query = '''
            WITH RECURSIVE category_tree AS (
                SELECT id FROM model_categories WHERE id = ?
                UNION ALL
                SELECT mc.id FROM model_categories mc
                JOIN category_tree ct ON mc.parent_id = ct.id
            )
            SELECT m.id, m.emission_source, m.description, m.data_source, m.emission_unit,
                   cf.name as factor_name, cf.emission_value
            FROM models m 
            LEFT JOIN carbon_factors cf ON m.factor_id = cf.id
            WHERE m.category_id IN (SELECT id FROM category_tree)
            ORDER BY m.emission_source
        '''
        model_based_sources = db.execute(category_query, (inventory['model_categories_id'],)).fetchall()
      
    return render_template('edit_inventory.html', 
                          inventory=inventory, 
                          model_categories=model_categories, 
                          inventory_categories=inventory_categories,
                          emission_sources=emission_sources,
                          model_based_sources=model_based_sources)
# 删除碳盘查记录
@inventory_bp.route('/delete/<int:id>', methods=['POST'])
def delete_inventory(id):
    db = get_db()
    try:
        # 先删除关联的排放源数据
        db.execute('DELETE FROM inventory_emission_sources WHERE inventory_id = ?', (id,))
        # 再删除碳盘查主记录
        db.execute('DELETE FROM carbon_inventory WHERE id = ?', (id,))
        db.commit()
        flash('碳盘查记录已成功删除', 'success')
    except Exception as e:
        db.rollback()
        flash(f'删除失败: {str(e)}', 'danger')
    return redirect(url_for('inventory.inventory_list'))

# 搜索模型
@inventory_bp.route('/search_models/<int:inventory_id>')
def search_models(inventory_id):
    query = request.args.get('query', '')
    db = get_db()
    
    try:
        # 获取当前盘查的模型分类ID
        inventory = db.execute('SELECT model_categories_id FROM carbon_inventory WHERE id = ?', (inventory_id,)).fetchone()
        if not inventory:
            return render_template('model_search_results.html', models=[])
        
        # 构建查询条件
        query_params = []
        where_clauses = []
        
        base_sql = '''
            SELECT m.id, m.emission_source, m.description, m.emission_unit, 
                   mc.id as category_id, mc.name as category_name, 
                   cf.id as factor_id, cf.name as factor_name 
            FROM models m 
            LEFT JOIN model_categories mc ON m.category_id = mc.id 
            LEFT JOIN carbon_factors cf ON m.factor_id = cf.id
        '''
        
        # 添加模型分类筛选
        if inventory['model_categories_id']:
            # 使用递归CTE查询所有子分类ID
            category_query = '''
                WITH RECURSIVE category_tree AS (
                    SELECT id FROM model_categories WHERE id = ?
                    UNION ALL
                    SELECT mc.id FROM model_categories mc
                    JOIN category_tree ct ON mc.parent_id = ct.id
                )
                SELECT id FROM category_tree
            '''
            category_ids = db.execute(category_query, (inventory['model_categories_id'],)).fetchall()
            if category_ids:
                category_id_list = [str(cat[0]) for cat in category_ids]
                where_clauses.append(f'm.category_id IN ({",".join(category_id_list)})')
        
        if query:
            where_clauses.append('(m.emission_source LIKE ? OR m.description LIKE ? OR cf.name LIKE ?)')
            query_params.extend([f'%{query}%'] * 3)
        
        if where_clauses:
            base_sql += ' WHERE ' + ' AND '.join(where_clauses)
        
        base_sql += ' ORDER BY mc.name, m.emission_source'
        
        models = db.execute(base_sql, query_params).fetchall()
        return render_template('model_search_results.html', models=models)
    except Exception as e:
        flash(f'搜索失败: {str(e)}', 'danger')
        return render_template('model_search_results.html', models=[])

# 添加排放源
@inventory_bp.route('/add_emission_source/<int:id>', methods=['POST'])
def add_emission_source(id):
    from flask import jsonify
    db = get_db()
    
    try:
        data = request.get_json()
        model_id = data.get('model_id')
        
        if not model_id:
            return jsonify({'error': '缺少模型ID'}), 400
        
        # 检查是否已关联
        existing = db.execute(
            'SELECT id FROM inventory_emission_sources WHERE inventory_id = ? AND emission_source_id = ?',
            (id, model_id)
        ).fetchone()
        
        if existing:
            return jsonify({'error': '该排放源已关联'}), 400
            
        # 添加新关联
        db.execute(
            'INSERT INTO inventory_emission_sources (inventory_id, emission_source_id) VALUES (?, ?)',
            (id, model_id)
        )
        db.commit()
        return jsonify({'message': '添加成功'}), 200
    except Exception as e:
        db.rollback()
        return jsonify({'error': str(e)}), 500
